if object_id('spgeninsert', 'P') > 0   drop procedure spgeninsert
go

Create Procedure dbo.spgeninsert (
	@from           varchar(255) = '',         -- 1
	@Flags 	        varchar(255) = 'V',        -- 2
	@to             varchar(255) = '',         -- 3
	@ignoreColumns  varchar(255) = '',         -- 4   semicolon separated list of colids or columnnames
	@whereclause	varchar(255) = ''          -- 5
) as 

SET NOCOUNT ON

-- part of Extreme T-SQL Script http://etsql.codeplex.com/
-- by Bernd Kriszio https://twitter.com/SQLSulidae  2010-11-02 
-- update: 2010-11-05
-- requires SQL-SERVER 2008: varchar(max), initialized variable declarations

DECLARE @flag_g    bit	= case when charindex ('G',  @FLAGS) > 0	then 1 else 0 end -- Generate meta script
DECLARE @flag_l    bit	= case when charindex ('L',  @FLAGS) > 0	then 1 else 0 end -- use new lines for each value
DECLARE @flag_o    bit  = case when charindex ('O',  @FLAGS) > 0	then 1 else 0 end -- ORACLE
DECLARE @flag_t    bit  = case when charindex ('T',  @FLAGS) > 0	then 1 else 0 end -- use transactions
DECLARE @flag_v8   bit  = case when charindex ('V8', @FLAGS) > 0 and @flag_o = 0	then 1 else 0 end -- Inserts with multiple values (SQL-Server 2008)  -- ToDo

DECLARE @max_values_per_insert int = 100 -- 1000 seems to be max supported

DECLARE @ora                char(1)      = LTRIM(str(@flag_o))
DECLARE @temporary          bit          = case when @flag_o = 0 and LEFT(@to, 1) = '@' then 1 else 0 end
Declare @has_autoval        bit          = 0
DECLARE @wcl                varchar(max) = case when isnull(@whereclause, '')  <> '' then ' where ' + @whereclause + ' ' else '' end
DECLARE @column_beg         varchar(10)  = case when @flag_l <> 0 then char(13) + char(10) + ' '  else '' end
DECLARE @close_beg          varchar(10)  = case when @flag_l <> 0 then char(13) + char(10)        else '' end
DECLARE @quoted_close_beg   varchar(30)  = case when @flag_l <> 0 then ' + CHAR(13)+ CHAR(10) '   else '' end

DECLARE @firstcol           sysname      = ''
DECLARE @insert_clause      varchar(max) = ''
DECLARE @column_name_list	varchar(max) = ''
DECLARE @statement          varchar(max) = ''
DECLARE @values             varchar(max) = ''
DECLARE @tablecolumns       varchar(max) = ''
DECLARE @statement_count    int 

DECLARE @column_id    int
DECLARE @column_name  sysname
DECLARE @format       varchar(30)
DECLARE @mstype       varchar(20)
DECLARE @is_identity  bit
DECLARE @line         nvarchar(max)
DECLARE @cursor_stmt  nvarchar(max)
DECLARE @curvar       cursor


DECLARE @tmp table(cnt int)				-- for results of dynamic exists querries

	if OBJECT_ID('tempdb..#columnInfo') is null
	begin  
		create table #columnInfo (
			column_id   int,
			name varchar(30),
			typ  varchar(20),
			max_length int,
			mstype varchar(20),
			oratype varchar(20),
			format varchar(30),
			scale int,
			is_nullable bit,
			is_identity bit,
			used_length int
		)
	end	


----------------------------------------------------------------------
-- defaults ----------------------------------------------------------
----------------------------------------------------------------------
if isnull(@to, '') = ''
	SET @to = @from

----------------------------------------------------------------------
-- Check existence of Tables -----------------------------------------
----------------------------------------------------------------------
delete @tmp
insert into @tmp (cnt)  exec ( 'select count(1) from sysobjects where type in (''U'', ''V'') and name = ''' + @from + '''')

if (select max(cnt) from @tmp) < 1
	begin
		print '##### Table unknown: ' + @from
		return
	end



----------------------------------------------------------------------
-- get colnames  -----------------------------------------------------
----------------------------------------------------------------------
exec sp_ColumnInfo @from


DECLARE c cursor for
select column_id, name, format, mstype, is_identity from #columnInfo order by column_id



open c
	Fetch next from c into @column_id, @column_name, @format, @mstype, @is_identity

	While @@FETCH_STATUS = 0
	Begin
		if 	charindex(';' + convert(varchar(5), @column_id) + ';', ';' + @ignoreColumns + ';', 1) < 1
		   and charindex(';' + @column_name + ';' , ';' + @ignoreColumns + ';', 1) < 1
		begin
			-- OK. In principal brackets are nothing than the reincarnation of quoted Identifiers 'the evil per se'. But I'm lazy i.e sometimes Bad Influenced too.
			-- Manualy used brackets are bad coding style induced by simple code generators (like this LOL)
			-- Remember tools do NOT generate nice code, it's enough when it's working
			if @firstcol = '' set @firstcol =  @column_name
			if @is_identity <> 0 set @has_autoval = 1
			set @tablecolumns = case when @tablecolumns = '' then  '' else @tablecolumns  + ', ' end +  @column_beg  + '[' + @column_name + '] ' + @mstype
			set @column_name_list = case when @column_name_list = '' then '' else @column_name_list + ', '  end + @column_beg + @column_name
			set @statement = case when @statement = '' then 'Select ''INSERT INTO ' + @to + '(' else @statement + ', '  end + @column_beg + @column_name
			set @values =    case when @values = ''    then case when @flag_v8 = 0 and @temporary = 0 then 'VALUES ' else '' end
				+'(''' else @values 	+ ' + '', '''    end 
				+ ' + ''' + @column_beg + '''' 
				+ CHAR(13) + CHAR(10) + ' + '  + replace (replace (@format, '/s/', @column_name), '/o/', @ora) 
		end
		Fetch next from c into @column_id, @column_name, @format, @mstype, @is_identity
	end
	
	if @temporary <> 0
	begin
		-- print '-- temporary'
		set @insert_clause = 'INSERT INTO ' + @to  + case when @flag_v8 <> 0 then ' VALUES' else '' end
		-- Print '--Insert_clause: ' + @insert_clause
		set @statement = case when @flag_v8 = 0 then 'Select ''INSERT INTO ' + @to + ' VALUES '  else 'Select ''' end
			+ @values + @quoted_close_beg  + '+ '')'' + ''''' + CHAR(13)+ CHAR(10) 
			+ ' FROM ' +  @from + @wcl + ' ORDER BY ' + @firstcol
		-- Print '-- Statement: ' + @statement		
	end		
	else
	begin
	
		set @insert_clause = 'INSERT INTO ' + @to + '(' + @column_name_list +  @close_beg + ')' + case when @flag_v8 <> 0 then ' VALUES' else '' end
		set @statement = 'Select ''' + @values +  @quoted_close_beg  + '+ '')'
			+  case when @flag_v8 = 0 then ';'' + CHAR(13)+ CHAR(10) + ''' else '' end
			+ ' ''' + CHAR(13)+ CHAR(10) 
			+ ' FROM ' +  @from + @wcl + ' ORDER BY ' + @firstcol
	end
close c
deallocate c


if  @flag_t <> 0
	if @flag_o = 0 
	begin
		PRINT ''
		PRINT 'BEGIN TRANSACTION;'
		PRINT ''
	end

if @temporary <> 0
begin
	Print 'DECLARE ' + @to + ' TABLE (' + @tablecolumns + @close_beg  +');'
	Print ''
end



if @flag_g <> 0     
	Print @statement
else	            
	begin
		if @has_autoval <> 0 and @flag_o = 0 and @temporary = 0
			Print 'SET IDENTITY_INSERT ' +  @to  + ' ON;' + char(13) + char(10) + ' '
	
		SET @statement_count = 0
		SET @cursor_stmt = 'set @curvar = CURSOR FOR ' + @statement + '; OPEN @curvar'
		EXEC sp_executesql  @cursor_stmt,  N'@curvar cursor OUTPUT', @curvar OUTPUT
		FETCH NEXT FROM @curvar into @line
		while @@fetch_status = 0
		begin
			SET @statement_count = @statement_count + 1
			--------------------------------------------
			if @temporary <> 0
			begin
				if @flag_v8 = 0
				begin
					Set @line = @line + ';'
				end	
				else
					if (@statement_count % @max_values_per_insert = 1)
					begin			
						print @insert_clause
						if @flag_v8 <> 0
							set @line = ' ' + @line
					end
					else
					begin			
						set @line = ',' + @line + case when @statement_count % @max_values_per_insert = 0 then ';'  + char(13) + char(10) + ' ' else '' end
					end
					exec print_string_in_lines @line
			end
			else
			begin
				if (@statement_count % @max_values_per_insert = 1) or @flag_v8 = 0
				begin			
					print @insert_clause
					if @flag_v8 <> 0
						set @line = ' ' + @line
					exec print_string_in_lines @line
				end
				else
				begin
					set @line = ',' + @line + case when @statement_count % @max_values_per_insert = 0 then ';' else '' end
					exec print_string_in_lines @line 
					if @statement_count % @max_values_per_insert = 0
						print '' 
				end
					
			end
			
			--------------------------------------------
			FETCH NEXT FROM @curvar into @line
			if  @statement_count % 100 = 0 and @flag_o <> 0 and @flag_t <> 0
			begin
				PRINT 'COMMIT;'
				PRINT 'PROMPT ' + str(@statement_count) + ';'
				PRINT ''
			end		
		end
		
		if (@statement_count % @max_values_per_insert <> 0) and @flag_v8 <> 0
			PRINT ';'
		
		
		if @has_autoval <> 0 and @flag_o = 0 and @temporary = 0
			Print 'SET IDENTITY_INSERT ' +  @to  + ' OFF;' + char(13) + char(10) + ' '

		if  @flag_t <> 0
			if @flag_o <> 0 
			begin
				PRINT 'COMMIT;'
				PRINT ''
			end
			else
			begin
				PRINT ''
				PRINT 'COMMIT TRANSACTION;'
				PRINT ''
			end
		
	end
go

/*
select * from int_vals
exec spgeninsert int_vals         , g
exec spgeninsert int_vals         , OG
exec spgeninsert int_vals         , LO

select * from num_vals
exec spgeninsert num_vals         , g
exec spgeninsert num_vals         , OG
exec spgeninsert num_vals         , LO

select * from string_vals
exec spgeninsert string_vals      , g
exec spgeninsert string_vals      , OG
exec spgeninsert string_vals      , LO

select * from date_vals
exec spgeninsert date_vals        , g
exec spgeninsert date_vals        , OG
exec spgeninsert date_vals        , LO

select * from other_vals
exec spgeninsert other_vals       , g
exec spgeninsert other_vals       , OG
exec spgeninsert other_vals       , LO

*/

